using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using SEO.Model;
using SEO.DBSqlHelp;

namespace SEO.DAL
{

    /// <summary>
    /// 版权所有: 版权所有(C) 2012，云里网络科技
    /// 内容摘要: EDMUnsubscribesDAL
    /// 完成日期：2012年8月15日
    /// 版    本：V1.0 
    /// 作    者：Adin
    /// </summary>
    public class EDMMailUnsubscribesDAL
    {

        /// <summary>
        /// 获取EDMUnsubscribes表中数据
        /// <returns>返回List</returns>
        /// </summary>
        public List<EDMMailUnsubscribes> GetAllEDMUnsubscribesDAL()
        {
            string strSql = "select * from EDMUnsubscribes";
            try
            {
                List<EDMMailUnsubscribes> list = new List<EDMMailUnsubscribes>();
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailUnsubscribes es = new EDMMailUnsubscribes();
                    es.UnsubId = int.Parse(dr["unsub_id"].ToString());
                    es.EmailAddress = dr["email_address"].ToString();
                    es.UserId = int.Parse(dr["user_id"].ToString());
                    es.PostTime = dr["post_time"].ToString();
                    es.ReadTime = dr["read_time"].ToString();
                    es.UnsubTime = Convert.ToDateTime(dr["unsub_time"].ToString());
                    es.ContentId = int.Parse(dr["content_id"].ToString());
                    es.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    es.ClientIp = dr["client_ip"].ToString();
                    es.ClientUrl = dr["client_url"].ToString();

                    list.Add(es);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据指定SQL语句获取EDMUnsubscribes表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回List</returns>
        public List<EDMMailUnsubscribes> GetEDMUnsubscribesByWhereDAL(string strSql)
        {
            try
            {
                List<EDMMailUnsubscribes> list = new List<EDMMailUnsubscribes>();
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataReader dr = cm.ExecuteReader();
                while (dr.Read())
                {
                    EDMMailUnsubscribes es = new EDMMailUnsubscribes();
                    es.UnsubId = int.Parse(dr["unsub_id"].ToString());
                    es.EmailAddress = dr["email_address"].ToString();
                    es.UserId = int.Parse(dr["user_id"].ToString());
                    es.PostTime = dr["post_time"].ToString();
                    es.ReadTime = dr["read_time"].ToString();
                    es.UnsubTime = Convert.ToDateTime(dr["unsub_time"].ToString());
                    es.ContentId = int.Parse(dr["content_id"].ToString());
                    es.SmtpId = int.Parse(dr["smtp_id"].ToString());
                    es.ClientIp = dr["client_ip"].ToString();
                    es.ClientUrl = dr["client_url"].ToString();

                    list.Add(es);
                }
                dr.Dispose();
                cn.Close();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 往EDMUnsubscribes表中插入数据
        /// <param name="es">实例化EDMUnsubscribes类</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int InsertEDMUnsubscribesDAL(SEO.Model.EDMMailUnsubscribes es)
        {
            int iNum = 0;
            string strSql = "insert into EDMUnsubscribes(email_address,user_id,post_time,content_id,smtp_id,client_ip,client_url) " +
             " values('" + es.EmailAddress + "'," + es.UserId + ",'" + es.PostTime + "'," + es.ContentId + "," + es.SmtpId + ",'" + es.ClientIp + "','" + es.ClientUrl + "')";
            try
            {
                iNum = SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("往EDMUnsubscribes表中插入数据 -> " + ex.Message);
            }

            return iNum;
        }

        /// <summary>
        /// 根据指定条件删除EDMUnsubscribes表中数据
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        /// <returns>返回影响行数</returns>
        public int DeleteEDMUnsubscribesDAL(string strSql)
        {
            try
            {
                return SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新EDMUnsubscribes表中数据
        /// </summary>
        /// <param name="es">实例化EDMUnsubscribes类</param>
        /// <returns>返回影响行数</returns>
        public int UpdateEDMUnsubscribesDAL(SEO.Model.EDMMailUnsubscribes es)
        {
            string strSql = "update EDMUnsubscribes set email_address='" + es.EmailAddress + "',user_id=" + es.UserId + ",post_time='" + es.PostTime + "',read_time='" + es.ReadTime + "',unsub_time='" + es.UnsubTime + "',content_id=" + es.ContentId + ",smtp_id=" + es.SmtpId + " where unsub_id=" + es.UnsubId;
            try
            {
                return SEO.DBSqlHelp.DBHelper.ExecuteSql(strSql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 绑定表EDMUnsubscribes到DataGridView
        /// <param name="dgv">DataGridView控件名称</param>
        /// <param name="strSql">SQL语句</param>
        /// </summary>
        public void BindEDMUnsubscribesDAL(System.Windows.Forms.DataGridView dgv, string strSql)
        {
            try
            {
                int iNum = 1;
                //清除最后自动生成的行
                dgv.AutoGenerateColumns = false;
                SqlConnection cn = new SqlConnection(SEO.DBSqlHelp.EDMDBHelper.ConnectionEDM());
                cn.Open();
                SqlCommand cm = new SqlCommand(strSql, cn);
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DataSet ds = new DataSet();
                da.Fill(ds, "EDMUnsubscribes");
                ds.Tables["EDMUnsubscribes"].Columns.Add("MailStatus", typeof(string));
                ds.Tables["EDMUnsubscribes"].Columns.Add("ViewID", typeof(string));
                ds.Tables["EDMUnsubscribes"].Columns.Add("ViewTitle", typeof(string));
                for (int i = 0; i < ds.Tables["EDMUnsubscribes"].Rows.Count; i++)
                {
                    DataRow dr = ds.Tables["EDMUnsubscribes"].Rows[i];
                    dr["ViewID"] = iNum;
                    dr["ViewTitle"] = "查看邮件详细内容";
                    iNum++;
                }
                dgv.DataSource = ds.Tables["EDMUnsubscribes"].DefaultView;
                da.Dispose();
                cn.Close();
                //清除当前选择行行为
                dgv.CurrentCell = null;

            }
            catch (Exception ex)
            {
                SEO.Utility.Log.SaveErrorLog("绑定EDMUnsubscribes -> " + ex.Message);
            }
        }
    }
}
